MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

Using Joins - Why aren't them working?

Discuss Using Joins - Why aren't them working? in the MS SQL Development forum on Dev Shed.
Using Joins - Why aren't them working? MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now!
  #1  
Old June 25th, 2004, 01:04 PM
talia679's Avatar
talia679 talia679 is offline
Always something else to learn
Dev Shed Novice (500 - 999 posts)
  
Join Date: Aug 2003
Location: PA
Posts: 691 talia679 User rank is Private First Class (20 - 50 Reputation Level)talia679 User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 1 Day 14 h 2 m 37 sec
Reputation Power: 5
Using Joins - results aren't what I expected
See this post for background info & to see the other things I tried:

http://forums.devshed.com/showthread.php?p=691829

----------------------------------------

I'm not sure how well I can explain this without writing a novel.

I want to get all of the subcategories for a given category and list them.

I have 2 tables for this, one is categories and one is subcategories:

categories table:

---------------------------
catid | title | descript |
---------------------------
1 | Forms | find forms |
---------------------------
2 | Legal | legal forms|
---------------------------
3 | Tax | tax forms |
---------------------------


subcategories table:

-----------------
catid | parent |
-----------------
2 | 1 |
-----------------
3 | 1 |
-----------------

I want to now select all of the subcategories for the Forms category. But this snippet of code below returns the parent row instead of the subcategories.


PHP Code:
 $query = "SELECT * FROM categories c LEFT JOIN
 subcategories sc ON c.catid = sc.parent WHERE c.catid = 1";

$catarray = mysql_query($query,$connect); 


The code above returns:

1 | Forms | find forms |

When I want it to return:

2 | Legal | legal forms|
---------------------------
3 | Tax | tax forms |
Last edited by talia679 : June 25th, 2004 at 01:36 PM.
Reply With Quote
  #2  
Old June 25th, 2004, 01:42 PM
Ucht's Avatar
Ucht Ucht is offline
This is only a test
Dev Shed Frequenter (2500 - 2999 posts)
  
Join Date: Mar 2003
Location: Off the air
Posts: 2,893 Ucht User rank is First Lieutenant (10000 - 20000 Reputation Level)Ucht User rank is First Lieutenant (10000 - 20000 Reputation Level)Ucht User rank is First Lieutenant (10000 - 20000 Reputation Level)Ucht User rank is First Lieutenant (10000 - 20000 Reputation Level)Ucht User rank is First Lieutenant (10000 - 20000 Reputation Level)Ucht User rank is First Lieutenant (10000 - 20000 Reputation Level)Ucht User rank is First Lieutenant (10000 - 20000 Reputation Level)Ucht User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 1 Month 5 Days 20 h 1 m 43 sec
Reputation Power: 147
What did you get when you tried this (reposting for convenience from PHP thread) --

Code:
SELECT  
  c3.catid,
  c3.title,
  c3.shortDescrip,
  c3.meta,
  c3.seealso
FROM
  categories c1
LEFT JOIN
  subcategories c2
ON
  c1.catid = c2.parent
LEFT JOIN
  categories c3
ON
  c3.catid = c2.catid
WHERE
  c1.catid = 1


I created some test tables and ran this, and it worked fine, though I was only going off of the name and descript columns.

[Edit]I just noticed this is MS SQL -- I assumed you were using MySQL, and the above may not be transferable. So, erm, nevermind.
__________________
"Not to offend our Swedish listeners ... if we have any, that is—"
"—But your team's rubbish."
(Sun webcast, Sweden vs. Paraguay)

Who needs corporate radio?
WeFunkRadio.com | Global Pop Conspiracy | Radio Paradise | SomaFM | The Classic Soul Network | Boot Liquor | WFMU Freeform Radio
Last edited by Ucht : June 25th, 2004 at 01:45 PM.
Reply With Quote
  #3  
Old June 25th, 2004, 01:52 PM
talia679's Avatar
talia679 talia679 is offline
Always something else to learn
Dev Shed Novice (500 - 999 posts)
  
Join Date: Aug 2003
Location: PA
Posts: 691 talia679 User rank is Private First Class (20 - 50 Reputation Level)talia679 User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 1 Day 14 h 2 m 37 sec
Reputation Power: 5
I posted in the wrong forum!!! OOPSS! I'll move my post to MySQL!!!
Reply With Quote
  #4  
Old June 25th, 2004, 01:52 PM
twostepted's Avatar
twostepted twostepted is offline
Contributing User
Dev Shed Novice (500 - 999 posts)
  
Join Date: Mar 2004
Location: Central Washington (USA)
Posts: 515 twostepted User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 21 h 50 m
Reputation Power: 5
Send a message via Yahoo to twostepted
Does this do the trick?
$query = "SELECT * FROM subcategories sc LEFT JOIN categories c
ON sc.parent=c.catid
WHERE sc.parent = 1";
Reply With Quote
  #5  
Old June 25th, 2004, 01:54 PM
talia679's Avatar
talia679 talia679 is offline
Always something else to learn
Dev Shed Novice (500 - 999 posts)
  
Join Date: Aug 2003
Location: PA
Posts: 691 talia679 User rank is Private First Class (20 - 50 Reputation Level)talia679 User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 1 Day 14 h 2 m 37 sec
Reputation Power: 5
Quote:
Originally Posted by Ucht
What did you get when you tried this (reposting for convenience from PHP thread) --

I created some test tables and ran this, and it worked fine, though I was only going off of the name and descript columns.


I got only 1 row and all of the results in it where NULL ... but I am using MySQL, not MS SQL ... and I'm tired so that's why I posted it in the wrong place!

Right post - Right Forum - http://forums.devshed.com/showthrea...1900#post691900
Reply With Quote
  #6  
Old June 25th, 2004, 01:56 PM
talia679's Avatar
talia679 talia679 is offline
Always something else to learn
Dev Shed Novice (500 - 999 posts)
  
Join Date: Aug 2003
Location: PA
Posts: 691 talia679 User rank is Private First Class (20 - 50 Reputation Level)talia679 User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 1 Day 14 h 2 m 37 sec
Reputation Power: 5
Quote:
Originally Posted by twostepted
Does this do the trick?
$query = "SELECT * FROM subcategories sc LEFT JOIN categories c
ON sc.parent=c.catid
WHERE sc.parent = 1";


I get 0 rows returned ...
Reply With Quote
  #7  
Old June 28th, 2004, 02:29 AM
Lethean Lethean is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2004
Location: Gothenburg Sweden
Posts: 28 Lethean User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Code:
$query = SELECT * 
FROM categories a, subcategories b 
WHERE a.catid = b.catid 
AND Parent = 1


Can't see why you need to bother with all the JOIN commands for this simple sql when a simple Carthesian works fine.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MS SQL Development > Using Joins - Why aren't them working?

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway.