is this possible

[Ujj]

Hello,just a quick question, it might be stupid one

 

is there any alternative to do same ?

 

SELECT b.price as totalPrice ,b.track_item_id ,  a.user_item , (select price as myprice where itemID=a.user_item)  from  tracked_item as a left join item_detail as b on b.track_item_id=a.id WHERE itemStatus='Active' AND b.user_id='$uid' GROUP BY b.track_item_id

[scootstah]

What are you trying to do?

[Ujj]

i am pulling b.price as totalPrice ,b.track_item_id from table b ,  a.user_item from table a  and and based on a.user_item on table a trying to pull price from table b  related to that a.user_item from single query.

[scootstah]

Is there a problem with that?

[Ujj]

Is there a problem with that?

 

yes

 

#1064 - 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 'where itemID=a.user_item) from tracked_item as a left join item_detail as b on' at line 1

[Jessica]

(select price as myprice where itemID=a.user_item)

 

This subquery is incomplete.

[Ujj]

(select price as myprice where itemID=a.user_item)

 

This subquery is incomplete.

 

any idea why its incomplete? do you mean the missing table (select price as myprice from item_detail where itemID=a.user_item  ) if yes; i tried this as well

[Jessica]

That's still not the correct syntax for a query.

Why not use a join, anyway?

[Ujj]

That's still not the correct syntax for a query.

Why not use a join, anyway?

 

yes something is wrong or something i don't know and i am looking for the answer

[Jessica]

Do you know correct syntax for a query? If not, look it up in the manual.

 

Edit: you changed your post, now the syntax is correct. Post your updated query and error.

[Ujj]

Do you know correct syntax for a query? If not, look it up in the manual.

 

Edit: you changed your post, now the syntax is correct. Post your updated query and error.

what is wrong in this query can you point it out for me please?

select price as myprice from item_detail where itemID=a.user_item 

[Jessica]

This is the best I can come up with given the info you've provided.

 

SELECT item_detail.price as totalPrice, 
item_detail.track_item_id,
tracked_item.user_item
item_detail.price as myprice

FROM tracked_item 

LEFT JOIN item_detail 
ON b.track_item_id=tracked_item.id 

LEFT JOIN item_detail item_detail_2
ON item_detail_2.itemID = tracked_item.user_item

WHERE ???.itemStatus='Active' 
AND item_detail.user_id='$uid' 

GROUP BY item_detail.track_item_id

 

If that doesn't do what you wanted, you'll need to post your tables and explain WHAT you want.

[mikosiko]

missing FROM.

 

but most likely you don't need the subquery.. you can JOIN the table item_detail twice using a different alias (Jesi just gave you that solution), or you just can add the condition b.itemID = a.user_item to your existing JOIN, which one to use is not clear reading your objectives.. :

Joining twice

    SELECT b.price as totalPrice ,
           b.track_item_id ,  
           a.user_item , 
           c.price as myprice
      from  tracked_item as a 
            left join item_detail as b on b.track_item_id = a.id 
            left join item_detail as c on c.itemID = a.user_item 
      WHERE itemStatus='Active' 
        AND b.user_id='$uid' 

 

Adding a condition to the actual JOIN

    SELECT b.price as totalPrice ,
           b.track_item_id ,  
           a.user_item , 
           b.price as myprice
      from  tracked_item as a 
            left join item_detail as b on b.track_item_id = a.id AND b.itemID = a.user_item
      WHERE itemStatus='Active' 
        AND b.user_id='$uid' 
      GROUP BY b.track_item_id

[Ujj]

thank you very much for the reply but i am looking for some different result

 

 

first i am pulling the detail of lowest prices item form item_detail grouped by tracked_item_id and then price of user_item to compare with that; currently i am using two queries to achieve this, but i want to reduce it into one query.

[Ujj]

any suggestion, idea for this please 

[kickstart]

Hi

 

Not 100% sure on what you want, but think something like this:-

 

SELECT b.itemID, b.price, c.MinPrice
FROM tracked_item a
INNER JOIN item_detail b
ON a.user_item_ = b.itemID
INNER JOIN (SELECT track_item_id, MIN(price) AS MinPrice FROM item_detail GROUP BY track_item_id) c
ON a.id = c.track_item_id

 

All the best

 

Keith

[Ujj]

 

Not 100% sure on what you want, but think something like this:-

 

 

Hi keith, thank you very very  much for the reply this what i was exactly looking but only one bit missing is the MinPrice

(SELECT track_item_id, MIN(price) AS MinPrice FROM item_detail GROUP BY track_item_id)

should exclude user_item.