Sql query with subselect?

Databases
1 
SELECT stl.storytitle
					 , stl.story_id
					 , stl.story_media
					 , stl.story_text
					 , stl.story_tags
					 , stl.story_date
					 , stl.category 
					 , stl.location
					 , p.photo_file
					 , p.photo_id
					 , s.cust_id AS shpID
					 , s.cust_customer AS shipper
					 , s.cust_address AS sAddress
					 , s.cust_city AS sCity
					 , s.cust_state AS sState
					 , s.cust_zip AS sZip
					 , s.coordinates AS sCoo
					 , rcv.cust_id AS rcvID
					 , rcv.cust_customer AS receiver
					 , rcv.cust_address AS rAddress
					 , rcv.cust_city AS rCity
					 , rcv.cust_state AS rState
					 , rcv.cust_zip AS rZip
					 , rcv.coordinates AS rCoo
					 , t.tripid
					 , t.lmiles
					 , t.tid
					 , t.pickup
					 , t.delivery
					 , t.emptystart
					 , c.category AS storyCat
					 , c.id AS catID
					 , t.trailer AS trailerid
					 , us.username
                     , us.picture
					 , us.id AS authorID
				  FROM storyline stl
				  
				 LEFT JOIN triplog t ON t.tid = stl.tid
				 LEFT JOIN customers s ON s.cust_id = t.shipper
				 LEFT JOIN customers rcv ON rcv.cust_id = t.receiver
				  RIGHT JOIN categories c ON c.id = stl.category
				  INNER JOIN users us ON us.id = stl.author
				   LEFT JOIN photos p ON p.photo_id = stl.story_media 
				 
				 WHERE stl.story_id LIKE ".$_GET["sid"]." 
							GROUP BY stl.story_id 
						ORDER BY stl.story_id ASC

This query lets me get what I need, but now I changed the triplog tables receiver column to comma separated values and I need to identify multiple ID’s in that field with the details stored in the customer table.

How would I do this ? I’m thinking I need a sub query ??

2

Don’t do that! Design a proper normalized Schema.

1 Like
3 
LEFT JOIN
RIGHT JOIN
INNER JOIN
LEFT JOIN

mein eyes!!

ze goggles… zey do nossink

wut

1 Like
5

Some trips have multiple receivers. I can’t think of any other way to include multiple receivers into this query. I allready got a table called “customers” and one called “triplogs”. Also my sql knowledge is very limited :smiley:

6

It was they only way I could figure out to get a complete set of what I needed. Some tables don’t contain all the data, but I still wanna retrieve them

before X
now X,X,X,X,X,X

7

Good thing you came to us before you hurt yourself, LOL!

So think about it, perhaps a “receivers” table with a foreign key tied to the trip id perhaps? If you can provide us a high level overview of what you have going on we can help you much better.

* Nice to see you know about the comma first format! By the way, you need to keep variables out of your queries. Use Prepared Statements.

8

Yes, I’m actually working on a tripreceiver table now. What I’m trying to do is a way for my blog posts to be interesting by providing everything each trip includes. Trip info, customers, blog posts, user who posted, photos taken in the date range of the start and end of the trip etc… There’s also a map that will include the route and photos so there’s lots of stuff i need to take from a variety of tables.

Now the original query does what i need, but when some trips have multiple receivers i ran into trouble with this query.

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.