Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

I need help on a select statement.

gogogogo Member Posts: 120
I have the 4 tables, for instance,

Table: Category
CategoryID int not null primary key
CategoryName varchar(64) not null

Table: Product
ProductID int not null primary key auto_increment
ProductName varchar(64) not null
ProductPrice int not null

Table: Author
AuthorID int not null primary key
AuthorName varchar(64) not null

Table: Category_Product_Author
CategoryID int not null reference Category
ProductID int not null reference Product
AuthorID int not null reference Author

I insert the values to them
CategoryID CategoryName
1 Adventure
2 Cooking
3 Mobile

ProductID ProductName
1 Latest mobile phone magazine
2 Amazon Adventure
3 How to cook Chinese food

AuthorID AuthorName
1 Tomas
2 Jeff
3 Shirley

CategoryID ProductID AuthorID
3 1 2

Then, I want to select the book under CategoryID = 3.

The result should display "Latest mobile phone magazine".

I made the following SQL statment

select c.CategoryName, p.ProductID, p.ProductName, a.AuthorName from category_product_author cpa inner join category c on cpa.CategoryID = 3 inner join product p on cpa.ProductID = p.ProductID inner join author a on cpa.AuthorID = a.AuthorID;

However, it lists all the CategoryName and duplicate the ProductID, ProductName and AuthorName like the following:

Adventure 1 Latest mobile phone magazine Jeff
Cooking 1 Latest mobile phone magazine Jeff
Mobile 1 Latest mobile phone magazine Jeff

But, what does the result that I want to show is only:

Mobile 1 Latest mobile phone magazine Jeff

How should I do that?

Thanks


Comments

  • JonathanJonathan Member Posts: 2,914
    :
    :
    : I made the following SQL statment
    :
    : select c.CategoryName, p.ProductID, p.ProductName, a.AuthorName from
    : category_product_author cpa inner join category c on cpa.CategoryID
    : = 3 inner join product p on cpa.ProductID = p.ProductID inner join
    : author a on cpa.AuthorID = a.AuthorID;
    :
    : However, it lists all the CategoryName and duplicate the ProductID,
    : ProductName and AuthorName like the following:
    :
    : Adventure 1 Latest mobile phone magazine Jeff
    : Cooking 1 Latest mobile phone magazine Jeff
    : Mobile 1 Latest mobile phone magazine Jeff
    :
    : But, what does the result that I want to show is only:
    :
    : Mobile 1 Latest mobile phone magazine Jeff
    :
    : How should I do that?
    I *think* you want to be doing this:-

    SELECT c.CategoryName, p.ProductID, p.ProductName, a.AuthorName
    FROM category_product_author cpa
    INNER JOIN category c ON c.CategoryID = cpa.CategoryID
    INNER JOIN product p ON cpa.ProductID = p.ProductID
    INNER JOIN author a ON cpa.AuthorID = a.AuthorID
    WHERE cpa.CategoryID = 3;

    Jonathan

    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • gogogogo Member Posts: 120
    Yes, thanks for help. I need WHERE cpa.CategoryID = 3
Sign In or Register to comment.