SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to use join on this?

    I want to add a column from another table on this and make it attach on the right.

    Here is my current query
    SELECT * FROM pvt_trades WHERE t_id = '{$_GET['t_id']}'

    How would I add this to that and make it attach to the right?
    SELECT * FROM `in` WHERE t_id = '{$_GET['t_id']}' AND ip_address = '$ip' AND date > DATE_SUB(CURDATE(),INTERVAL 24 hour)

    That column name would be last_24hrs

    How would I do this? Help is much appreciated.
    Have a good day.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what column do the two tables have in common?

  3. #3
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    None... I need to add the column in the query...
    Have a good day.

  4. #4
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this even possible? I tried sub queries and it didnt work...

    I have 3 tables

    global
    |t_id|title|domain|url|

    private
    |p_id|t_id|title|domain|url|

    in
    |in_id|t_id|info|date|

    I want to combine all those to make this
    |combined_type|combined_id|combined_title|combined_domain|combined_url|combined_last_24hrs|

    Where the total count for the last 24 hours is taken from the in table and for global its where t_id = t_id and for private its for where t_id = p_id.

    Code:
    SELECT 'glob' AS combined_type, t_id AS combined_id, title AS combined_title, domain AS combined_domain, url AS combined_url, (SELECT * FROM `in` WHERE t_id = 't_id' AND date > DATE_SUB(CURDATE(),INTERVAL 24 hour))) AS combined_last_24hrs FROM global WHERE t_id <> '1' AND category = 'category' UNION ALL SELECT 'prvt', trade_id, title, domain, url, (SELECT * FROM `in` WHERE t_id = 'trade_id' AND date > DATE_SUB(CURDATE(),INTERVAL 24 hour))) FROM private WHERE t_id = '1' ORDER BY combined_last_24hrs DESC LIMIT 0,20
    That is my current query it will not work, it has an error where the sub query is wrong. How do I make this work? Please help. :'(
    Have a good day.

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,
    which version are you using?
    remove quotes ( ' ) from around t_id in your subquery.
    may i ask you why you want to add a column from a table to a select from another table which these tables have no relation to each other?

    Chagh

  6. #6
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so i can display them
    Have a good day.

  7. #7
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi I did what you said and it didnt work...

    Rudy are you here can you help me plzzzz...
    Have a good day.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    darren, you're in the US? according to the timestamps i see, you posted in the middle of the night, i was here but i was sleeping

    please show me a few rows of data from global
    |t_id|title|domain|url|

    then please show me a few rows of data from private
    |p_id|t_id|title|domain|url|
    and please make sure some of the values of t_id relate to global

    then please show me a few rows of data from in
    |in_id|t_id|info|date|
    and please make sure some of the values of t_id relate to global

    finally, please show the result set that you expect to get from the sample data you've shown
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it doesn't need to use join any more that is what I am saying... I do not know how to do attach this on as a column...
    Have a good day.

  10. #10
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will show where the date will get where it is suppose to come from by bolding it.

    SELECT 'glob' AS combined_type, t_id AS combined_id, title AS combined_title, domain AS combined_domain, url AS combined_url, (SELECT * FROM `in` WHERE t_id = t_id AND date > DATE_SUB(CURDATE(),INTERVAL 24 hour))) AS combined_last_24hrs FROM global WHERE t_id <> '1' AND category = 'category' UNION ALL SELECT 'prvt', trade_id, title, domain, url, (SELECT * FROM `in` WHERE t_id = trade_id AND date > DATE_SUB(CURDATE(),INTERVAL 24 hour))) FROM private WHERE t_id = '1' ORDER BY combined_last_24hrs DESC LIMIT 0,20

    Those bold both come from the before select statement.
    Have a good day.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i am totally lost, sorry, no idea what you're doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    are you trying to get all of the trades from the last 24 hours?
    Code:
    SELECT
    		'glob' AS combined_type
    	, g.t_id AS combined_id
    	, g.title AS combined_title
    	, g.domain AS combined_domain
    	, g.url AS combined_url
    	, i.date AS combined_last_24hrs
    FROM global g
    JOIN `in` i ON
    	i.t_id = g.t_id
    	AND i.date > DATE_SUB(CURDATE(),INTERVAL 24 hour)
    
    UNION ALL
    
    (SELECT
    		'prvt'
    	, p.trade_id
    	, p.title
    	, p.domain
    	, p.url
    	, i.date
    FROM private p
    JOIN `in` i ON
    	i.t_id = g.t_id
    	AND i.date > DATE_SUB(CURDATE(),INTERVAL 24 hour))
    
    ORDER BY combined_last_24hrs DESC
    next time you post a query, format it! please!

  13. #13
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi I need the combined_last_24hrs not to be the date, but to be the sum of rows that comes up for the id... how would I do that?
    Have a good day.

  14. #14
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Example this would be for the combined_last_24hrs
    SELECT COUNT( * )
    FROM `in`
    WHERE i.t_id = g.t_id AND date > DATE_SUB( CURDATE( ) , INTERVAL 24 HOUR )

    How would I do this?
    Have a good day.

  15. #15
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    your quert in #4 was pretty close, but you never actually told mysql to count the rows. that's why you were getting the error. when you put a subquery in the select portion of the parent query, that query can only return one row and one column. so instead of *, you should have used count(*).
    Code:
    SELECT
    		'glob' AS combined_type
    	, g.t_id AS combined_id
    	, g.title AS combined_title
    	, g.domain AS combined_domain
    	, g.url AS combined_url
    	, (SELECT COUNT( * )
    		 FROM `in`
         WHERE i.t_id = g.t_id
    		   AND date > DATE_SUB( CURDATE( ) , INTERVAL 24 HOUR ) AS combined_last_24hrs
    FROM global g
    
    UNION ALL
    
    (SELECT
    		'prvt'
    	, p.trade_id
    	, p.title
    	, p.domain
    	, p.url
    	, (SELECT COUNT( * )
    		 FROM `in`
         WHERE i.t_id = g.t_id
    		   AND date > DATE_SUB( CURDATE( ) , INTERVAL 24 HOUR )
    FROM private p)
    
    ORDER BY combined_last_24hrs DESC

  16. #16
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi I get an error:
    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*)
    FROM `in`
    WHERE i.t_id = g.t_id
    Have a good day.

  17. #17
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    what version of mysql are you using? you can find out by doing SELECT VERSION()

  18. #18
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    4.0.24-standard
    Have a good day.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please see this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard Darren884's Avatar
    Join Date
    Aug 2003
    Location
    Southern California, United States
    Posts
    1,616
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright guys I will have it updated soon.
    Have a good day.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •