Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    16

    Unanswered: Question about a query...

    Hi. i want to select all column values in an AS statement.

    Select * from myTable1 AS mt1
    myTable2 as mt2
    WHERE mt1.ID=mt2.ID

    I want to select all column values of myTable1 where mt1.ID=mt2.ID

    I mean what does * mean there?

    The table name first written hence here it is mt1,
    or the table name last written hence here it is mt2.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please give sample data, and also the results that you want from the query

    it looks like you're joining a table to itself, and it also looks like ID might be the primary key of the table...

    ... and if that's true, then your query doesn't make sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    258
    With your query, "*" means all fields in both myTable1 and myTable2. A better way to write the SQL statement and only get the myTable1 fields would be as follows:
    Code:
    SELECT mt1.* FROM myTable1 AS mt1
    INNER JOIN myTable2 AS mt2
    ON mt1.ID=mt2.ID
    Regards,

    Ax

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xotesa
    Can't i write the query that you wrote with aliasas?
    yes, you can

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    16
    Thanks guys.

    Can't i write the query that you wrote with aliasas?

  6. #6
    Join Date
    Jun 2009
    Posts
    16
    Select mt1.* from myTable1 AS mt1
    myTable2 as mt2
    WHERE mt1.ID=mt2.ID


    So what i wrote above is true?

    Also do i need pk-fk relationship for joins?

    Like

    select mt1.* from myTable1 as mt1
    inner join
    myTable2 as mt2
    where mt1.age=mt2.weight

    It's like i want to select columns where an animal's weight is equal to a person's age. No PK-FK relationship at all?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xotesa
    Also do i need pk-fk relationship for joins?
    please see post #2

    we ~still~ don't know what you're trying to do

    show us your real table and state your real problem, not this "mytable" stuff
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2009
    Posts
    16
    Ok let me clarify my question.

    I'm not trying to do anything. I am just asking a question about joins.

    As an example.

    I have an animal table and a person table.

    I want to select people whose age is equal to an animals weight.

    There is no PK-FK relationship.

    Can I do it with inner join?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xotesa
    There is no PK-FK relationship.

    Can I do it with inner join?
    certainly

    you can write a join query to join whatever tables on whatever columns you wish

    whether the query will return anything meaningful is another matter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2009
    Posts
    258
    As long as the data types of the fields you are joining on are the same between the two files, yes, you can do nonsensical operations like that.

  11. #11
    Join Date
    Jun 2009
    Posts
    16
    Thank you for you answers.

    The answer is what i am looking for.

    In fact it's not nonsense for me to ask a question like this. Because now when i do an inner join, i won't be asking if they have any pk-fk relationship. I was thinking about if they have relationship or not.

    For example, if a table has a column as userID and another table userID as well in the same database, i was looking for if they have relationship or not. Now i will feel free to make joins and the results will be meaningful.

    Thank you again.

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Ax238
    As long as the data types of the fields you are joining on are the same between the two files, yes, you can do nonsensical operations like that.
    You can even do joins on different column types if you'd like, although you might then get nonsensical results or no results, depending on the contents of the columns.

    That's one of the inherent wonders of SQL, you can use a virtually limitless toolbox of nonsensical operations and be rewarded with a veritable cornucopia of nonsensical results!

    I LOVE this database!!!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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